package main

import (
	"fmt"
	"log"
	"time"

	"github.com/astaxie/beego/orm"
	_ "github.com/go-sql-driver/mysql"
)

type User struct {
	Id        int64      `orm:"column(uid);pk;auto"`
	Name      string     `orm:"size(64)"`
	Password  string     `orm:"size(1024)"`
	Tel       string     `orm:"size(32)"`
	Addr      string     `orm:"type(text)"`
	Birthday  *time.Time `orm:"type(date)"`
	CreatedAt *time.Time `orm:"auto_now_add"`
	UpdatedAt *time.Time `orm:"auto_now"`
	DeletedAt *time.Time `orm:"null"`
}

func main() {
	orm.Debug = true
	// 导入驱动
	// 导入orm包
	// 再orm包中注册驱动（mysql自动注册）
	orm.RegisterDriver("mysql", orm.DRMySQL) //自动注册，可以省略
	// 注册数据库（配置信息）
	dsn := "golang:zxc,./123@tcp(43.139.115.142:3306)/testorm?charset=utf8mb4&loc=Local&parseTime=true"
	////default 别名 必须要一个默认的别名，mysql 类型 与驱动名对应，dsn 连接信息

	if err := orm.RegisterDataBase("default", "mysql", dsn); err != nil {
		log.Fatal(err)
	}
	// 注册模型（结构体）
	orm.RegisterModel(&User{}) // 必须指针类型实例
	// 操作
	// DDL DML DQL
	// orm.RunSyncdb("default", false, true) // default 别名，第二个参数是否执行的时候先删除表，第三个显示详情信息

	//DML DQL

	// 增
	/*
		birthday, _ := time.Parse("2006-01-02", "1997-01-20")

		// 单条插入
		user := &User{
			Name:     "sonfer",
			Password: "123345",
			Birthday: &birthday,
		}
		fmt.Println(ormer.Insert(user))

		// 批量插入
		users := []*User{}
		for i := 0; i < 10; i++ {
			users = append(users,
				&User{
					Name:     "sonfer",
					Password: "123345",
					Birthday: &birthday,
				},
			)
		}
		ormer.InsertMulti(5, users)
	*/

	// 查数据
	// 单挑数据查询
	/*
		user := &User{
			Id: 10,
		}
		fmt.Println(ormer.Read(user))
		fmt.Println(user)
		// 默认找id 主键
		// 只找一行数据，多条数据找第一条
		user = &User{Name: "sonfer", Password: "123345"}
		// read后面参数接需要查找的字段
		fmt.Println(ormer.Read(user, "Name", "Password"))
		fmt.Println(user)
	*/

	// 修改数据
	// 需要先查到数据
	/*
		user := &User{
			Id: 10,
		}
		ormer.Read(user)
		user.Name = "sonfer1111"
		user.Password = "123456"
		fmt.Println(ormer.Update(user))

		user.Name = "sonfer123"
		user.Password = "12345232"
		fmt.Println(ormer.Update(user, "Name"))
	*/
	// 删除数据
	/*
		user := &User{Id: 10, Name: "sonfer1"}
		fmt.Println(ormer.Delete(user, "Name", "Id"))
	*/

	/*
		birthday, _ := time.Parse("2006-01-02", "1997-01-20")

		user := &User{
			Id:       9,
			Name:     "sonfer",
			Password: "123345",
			Birthday: &birthday,
		}
		// 没有就创建，有就读取
		// fmt.Println(ormer.ReadOrCreate(user, "Name"))

		// 存在就更新，不存在就插入
		ormer.InsertOrUpdate(user, "Id", "Name", "Password", "Birthday")
	*/

	// 满足条件 filter
	// 去掉满足条件 Exclude

	// Count
	// 数据
	/*
		queryset := ormer.QueryTable(&User{})
		fmt.Println(queryset.Count())

		queryset = queryset.Filter("Id__gt", 10).Filter("Id__lt", "15")
		var users []*User
		fmt.Println(queryset.All(&users, "Name", "Id"))
		fmt.Println(users)
		for _, v := range users {
			fmt.Println(*v)
		}
	*/

	// Condition
	/*
		cond := orm.NewCondition()
		cond = cond.Or("Tel__iexact", "").Or("Id__lt", 10)

		queryset := ormer.QueryTable(&User{})

		queryset = queryset.SetCond(cond)

		fmt.Println(queryset.Count())
		var users []*User
		fmt.Println(queryset.All(&users))
	*/
	// 批量更新
	/*
		queryset := ormer.QueryTable(&User{})
		queryset = queryset.Filter("Id__gt", "10")

		queryset.Update(orm.Params{
			"tel":      "123456",
			"addr":     "xxxx",
			"birthday": orm.ColValue(orm.ColAdd, 1), // 原有数据+1
		})
	*/
	// 批量删除
	/*
		queryset := ormer.QueryTable(&User{})
		queryset.Filter("Id__lt", "10").Delete()
	*/
	// 执行原生sql
	// 查询
	/*
		ormer := orm.NewOrm()
		rs := ormer.Raw("select * from user where uid > ?", 15)
		var users []*User
		rs.QueryRows(&users)
		for _, u := range users {
			fmt.Println(*u)
		}

		var result []orm.Params
		rs = ormer.Raw("select name,count(*) as cnt from user group by name")
		rs.Values(&result)
		for _, u := range result {
			fmt.Println(u)
		}

		var list []orm.ParamsList
		rs = ormer.Raw("select name,count(*) as cnt from user group by name")
		rs.ValuesList(&list)
		for _, u := range list {
			fmt.Println(u)
		}
	*/
	// 更新
	ormer := orm.NewOrm()
	rs := ormer.Raw("update user set tel=?", "xxxxx123")
	fmt.Println(rs.Exec())

	// 执行事务
	if err := ormer.Begin(); err == nil {
		// 没错误
		ormer.Commit()
		// 有错误
		ormer.Rollback()
	}
}
